#!/home/berald01/.local/bin/python

import sys
import psycopg2
import os
import argparse
import re
import sblab
import traceback

parser = argparse.ArgumentParser(description= """
DESCRIPTION
    Upload bamqc.py report to sblab.main.bamqc. Only rows not already in bamqc are imported.

EXAMPLE:
Execute and upload bamqc on lsf:
 
        cd /lustre/sblab/berald01/repository/bam
        for bam in ear0[0-1][05-9]*.bam
        do
        bsub "bamqc.py -i $bam --nograph --noheader -o ${bam/.bam/.bamqc}
        upload_bamqc.py ${bam/.bam/.bamqc}" 
        done

    """, formatter_class= argparse.RawTextHelpFormatter)

parser.add_argument('report',
                   help='''The bamqc report to upload.
                   ''')

parser.add_argument('--skip', '-s',
                   required= False,
                   default= 0,
                   type= int,
                   help='''Skip this many lines from report, default 0. Set to 1 if report has header.
                   ''')

parser.add_argument('--nocommit',
                   action= 'store_true',
                   help='''Don't commit changes to database. I.e. just test
whether the report can be imported without problems.
                   ''')
                   
parser.add_argument('--verbose', '-v',
                   action= 'store_true',
                   help='''Show what is being executed.
                   ''')

args = parser.parse_args()

# ------------------------------------------------------------------------------

report= open(args.report).readlines()
report= report[args.skip:]
report= [x.strip() for x in report]

conn= psycopg2.connect(sblab.get_psycopgpass())
cur= conn.cursor()

sql= 'CREATE TEMP TABLE bamqc_report_tmp (LIKE bamqc);'
if args.verbose:
    print(cur.mogrify(sql))
cur.execute(sql)
for line in report:
    line= line.split('\t')
    nfields= ', '.join(['%s'] * len(line))
    sql= 'INSERT INTO bamqc_report_tmp VALUES (%s);' %(nfields,)
    if args.verbose:
        print(cur.mogrify(sql, line))
    cur.execute(sql, line)
    
sql= 'INSERT INTO bamqc (SELECT DISTINCT * FROM bamqc_report_tmp EXCEPT SELECT * FROM bamqc); DROP TABLE bamqc_report_tmp;'
if args.verbose:
    print(cur.mogrify(sql))
cur.execute(sql)

if args.nocommit:
    print('\nChanges have not been commited.\n')
else:
    conn.commit()
cur.close()
conn.close()
sys.exit()
